﻿using System;
using System.Collections.Generic;
using System.IO;
using System.Data;
using System.Xml;
using SdmxMl.SmsImportCfg;
using SdmxMl.Common;
using SdmxMl.Structure;
using SdmxMl.Manager;
using SdmxMl.GenericMetadata;

namespace SdmxMl.SmsImportCfg
{
    public class SakeVar
    {
        private ExcelDataReader.ExcelDataReader spreadsheetConcept = null;

        private ConceptSchemeType cst;
        public SmManager sm;
        private SakeExcelColumns colIndex;

        private ExcelDataReader.ExcelDataReader LoadSources(string propertyPath)
        {
            ExcelDataReader.ExcelDataReader spreadsheet = null;
            if (propertyPath == null)
                throw new ApplicationException("Please provides path.");

            FileStream fs = null;
            try
            {
                fs = new FileStream(propertyPath, FileMode.Open, FileAccess.Read, FileShare.Read);
                spreadsheet = new ExcelDataReader.ExcelDataReader(fs);
            }
            finally
            {
                if (fs != null)
                    fs.Close();
            }

            return spreadsheet;
        }

        #region SAKE Vars

        public SakeVar()
        {
            spreadsheetConcept = LoadSources(@"C:\Doc\SAKE\Main_variables.xls");
            int theYear = 2012;
            colIndex = new SakeExcelColumns(theYear);
            cst = new ConceptSchemeType();
            cst.Id = "CS_SAKE_VAR";
            cst.Name.UpdateForLanguage("de", "Gesamtansicht der SAKE Variablen");
            cst.Name.UpdateForLanguage("fr", "Vue d'ensemble des variables ESPA");
            cst.AgencyId = "CH1_SAKE";
            cst.Version = theYear.ToString() + ".0";

       }

 
        /// <summary>
        /// 
        /// </summary>
        public void ConstructVars()
        {

            // List of VarProto, independant of year
            // Must be located unique worksheet
            DataTable dt = spreadsheetConcept.WorkbookData.Tables[1];
            WorkSheetHelper wh = new WorkSheetHelper(dt);

            ConceptType cParent = null;
            bool subParent = false;

            for (int row = 1; row <= dt.Rows.Count; row++)
            {
                subParent = false;
                if (row > 2) // 1 header rows
                {
                    string parent = wh.GetCellContent(row, 1);
                    int ndx = parent.IndexOf(")");
                    if (ndx < 0)
                    {
                        parent = wh.GetCellContent(row, 2);
                        ndx = parent.IndexOf(")");
                        subParent = true;
                    }
                    if (ndx == 1 || ndx == 2)
                    {
                        string par = subParent ? cParent.Id : string.Empty;
                        cParent = new ConceptType();
                        cParent.Id = parent.Substring(0, ndx);
                        cParent.Parent = par;
                        string[] arr = parent.Substring(ndx + 1).Split('/');
                        if (arr.Length == 2)
                        {
                            cParent.Name.UpdateForLanguage("de", arr[0]);
                            cParent.Name.UpdateForLanguage("fr", arr[1]);
                        }
                        else if (arr.Length == 1)
                        {
                            cParent.Name.UpdateDefaultText(arr[0]);
                        }
                        cst.ConceptList.Add(cParent);
                        cst.ConceptList.HasHierarchy = true;
                    }
                    string varProto = wh.GetCellContent(row, colIndex.Id);
                    if (varProto.Length > 0)
                    {
                        ConceptType c = new ConceptType();
                        c.Id = varProto;
                        if (cParent != null)
                            c.Parent = cParent.Id;

                        c.Name.UpdateForLanguage("de", wh.GetCellContent(row, colIndex.NameDe).Replace("\"", ""));
                        c.Name.UpdateForLanguage("fr", wh.GetCellContent(row, colIndex.NameFr).Replace("\"", ""));

                        string dec = wh.GetCellContent(row, colIndex.Decimal);
                        string an = wh.GetCellContent(row, colIndex.Annual);
                        string maxLen = wh.GetCellContent(row, colIndex.MaxLen);
                        if (dec.Length > 1 && dec[0] == '.')
                        {
                            c.TextFormat.TextType = TextTypeType.Decimal;
                            c.TextFormat.maxLength = maxLen;
                            c.TextFormat.decimals = dec.Substring(1);
                        }
                        else
                        {
                            c.TextFormat.TextType = TextTypeType.Integer;
                            c.TextFormat.maxLength = maxLen;
                        }
                        if (an.Length > 0)
                        {
                            Annotation a = new Annotation("PERIOD");
                            a.Text.UpdateDefaultText(an);
                            c.AnnotationList.Add(a);
                        }

                        cst.ConceptList.Add(c);

                    }
                }
            }

            // Save
            sm = new SmManager();
            sm.PutArtefact(SdmxArtefactType.Concepts, cst);
            //XmlDocument xDoc = sm.GetAsDocument();
            //xDoc.Save(@"C:\doc\" + cst.FullIdent + ".xml");
        }
        #endregion

        private string CleanCodeText(string txt)
        {
            string codeText = txt.Replace("\"", "");
            int ndx = codeText.IndexOf('.');
            return codeText.Substring(ndx+1);
        }

        public void ConstructCodes(ConceptSchemeType conceptscheme)
        {
            cst = conceptscheme;
            sm = ReferenceManager.GetSmContainerImage();
          //  sm.PutArtefact(SdmxArtefactType.Concepts, cst);

            spreadsheetConcept = LoadSources(@"C:\Doc\SAKE\FORMATE2013-Module.xls");
            DataTable dt = spreadsheetConcept.WorkbookData.Tables[1];
            WorkSheetHelper wh = new WorkSheetHelper(dt);
            ConceptType cOwner = null;
            CodeListType cltCode = null;
            CodeListType cltQuality = null;

            string previousVarId = string.Empty;

            for (int row = 2; row <= dt.Rows.Count; row++)
            {
                string varId = wh.GetCellContent(row, 1);
                if (varId.Length > 0)
                {
                    UpdateCodelistContainer(cltCode, cltQuality, cOwner);
                    cOwner = cst.ConceptList.LocateItem(varId.ToUpper()) as ConceptType;
                    cltCode = new CodeListType();
                    cltCode.Id = "CL_" + varId.ToUpper();
                    cltCode.AgencyId = "CH1_SAKE";
                    cltCode.Name.UpdateDefaultText("Codelist for : " + varId.ToUpper());

                    cltQuality = new CodeListType();
                    cltQuality.Id = "CL_QUAL_" + varId.ToUpper();
                    cltQuality.AgencyId = "CH1_SAKE";
                    cltQuality.Name.UpdateDefaultText("Quality Codelist for : " + varId.ToUpper());
                }

                string code = wh.GetCellContent(row, 2);
                string de = wh.GetCellContent(row, 4);
                string fr = wh.GetCellContent(row, 5);

                CodeType c = new CodeType();
                c.Id = code;
                c.Name.UpdateForLanguage("de", CleanCodeText(de));
                c.Name.UpdateForLanguage("fr", CleanCodeText(fr));

                if (code[0] == '-')
                    cltQuality.CodeList.Add(c);
                else
                     cltCode.CodeList.Add(c);
           }
           UpdateCodelistContainer(cltCode, cltQuality, cOwner);

           XmlDocument xdoc = sm.GetAsDocument();
           xdoc.Save(@"C:\doc\sake\aaa.xml");

        }

        private void UpdateCodelistContainer(CodeListType cltCode, CodeListType cltQuality, ConceptType c)
        {
            CodeListType clt = null;
            string va = c != null ? c.Id : "?";

            if (cltCode != null && cltCode.CodeList.Count > 0 )
             //   if (cltCode != null && cltCode.CodeList.Count > 0 && !(cltQuality != null && cltQuality.CodeList.Count == cltCode.CodeList.Count))
            {
                clt = CodelistAlreadyDefined(cltCode);
                if (clt != null)
                    clt.Name.UpdateDefaultText(clt.Name.ToString() + ", " + va);
                else
                {
                    clt = cltCode;
                    sm.PutArtefact(SdmxArtefactType.CodeLists, cltCode);
                }
                if (c != null)
                {
                    // refere codelist in concept
                    c.CoreRepresentation.Id = clt.Id;
                    c.CoreRepresentation.AgencyId = clt.AgencyId;
                }
            }

           
            clt = null;

             if (cltQuality != null && cltQuality.CodeList.Count > 0 )
             //   if (cltQuality != null && cltQuality.CodeList.Count > 0 && (cltCode == null || cltQuality.CodeList.Count == cltCode.CodeList.Count))
            {
                clt = CodelistAlreadyDefined(cltQuality);
                if (clt != null)
                    clt.Name.UpdateDefaultText(clt.Name.ToString()); // + ", " + va);
                else
                {
                    clt = cltQuality;
                    sm.PutArtefact(SdmxArtefactType.CodeLists, cltQuality);
                }
                if (c != null)
                {
                    ConceptType cFlag = new ConceptType();
                    cFlag.Id = c.Id + "_F";
                    cFlag.Parent = c.Id;
                    cFlag.Name.UpdateDefaultText("Flag for " + c.Id);
                    cst.ConceptList.Add(cFlag);
                    // refere codelist in concept
                    cFlag.CoreRepresentation.Id = clt.Id;
                    cFlag.CoreRepresentation.AgencyId = clt.AgencyId;
                }
            }
             
            
        }

        private CodeListType CodelistAlreadyDefined(CodeListType newCodelist)
        {
            ArtefactManager amgr = sm.GetArtefactManager(SdmxArtefactType.CodeLists);
            foreach (CodeListType clt in amgr.ArtefactList)
                if (SameCodes(newCodelist.CodeList, clt.CodeList))
                    return clt;
            return null;
        }

        private bool SameCodes(CodeList a, CodeList b)
        {
            bool sameCodes = false;
            if (a.Count == b.Count)
            {
                sameCodes = true;
                for (int c = 0; c < a.Count; c++)
                {
                    if (a[c].Id != b[c].Id)
                    {
                        sameCodes = false;
                        break;
                    }
                    else
                    {
                        foreach (string lg in LangSupport.SupportedLanguages)
                        {
                            if (lg != "de" && lg != "fr")
                                continue;
                            if (a[c].Name.GetForLanguage(lg) != b[c].Name.GetForLanguage(lg))
                            {
                                sameCodes = false;
                                break;
                            }
                        }
                        if (sameCodes == false)
                            break;
                    }
                }
            }

            return sameCodes;
        }

        public void ConstructAnnot(ConceptSchemeType conceptscheme, string propertyPath)
        {
            cst = conceptscheme;
            sm = new SmManager();
            sm.PutArtefact(SdmxArtefactType.Concepts, cst);

            spreadsheetConcept = LoadSources(propertyPath);

            DataTable dt = spreadsheetConcept.WorkbookData.Tables[0];
            WorkSheetHelper wh = new WorkSheetHelper(dt);
            string previousVarId = string.Empty;

            for (int row = 1; row <= dt.Rows.Count; row++)
            {
                string varId = wh.GetCellContent(row, 2);
                if (varId.Length > 0)
                {
                    ConceptType c = cst.ConceptList.LocateItem(varId) as ConceptType;
                    if (c != null)
                    {
                        // Parent reattribution
                        string newParent = wh.GetCellContent(row, 3).ToUpper();
                        if (newParent.Length > 0)
                        {
                            c.Parent = newParent;
                        }
                        
                        // Quest Print
                        string qp = wh.GetCellContent(row, 74);
                        if (qp.Length > 0)
                        {
                            Annotation a = new Annotation("QUEST_PRINT");
                            a.Title = qp;
                            c.AnnotationList.Add(a);
                        }
                        
                        /*
                        // TextFormat
                        string len = wh.GetCellContent(row, 5);

                        c.TextFormat.maxLength = len;
                        string fmt = wh.GetCellContent(row, 6);
                        if (fmt.Length > 0 && fmt[0] == '.')
                        {
                            c.TextFormat.TextType = TextTypeType.Decimal;
                            c.TextFormat.decimals = fmt.Substring(1);
                        }
                        else
                        {
                            c.TextFormat.TextType = TextTypeType.Integer;
                        }


                        // Add annotations
                        string indic_d = wh.GetCellContent(row, 7);
                        string indic_f = wh.GetCellContent(row, 8);
                        if (indic_d.Length > 0 || indic_f.Length > 0)
                        {
                            Annotation a = new Annotation("INDIC_SUP");
                            a.Text.UpdateForLanguage("de", indic_d);
                            a.Text.UpdateForLanguage("fr", indic_f);
                            c.AnnotationList.Add(a);
                        }

                        string vague = wh.GetCellContent(row,33);
                        if (vague.Length > 0)
                        {
                            Annotation a = new Annotation("VAGUE");
                            a.Title = vague;
                            c.AnnotationList.Add(a);
                            indic_d = wh.GetCellContent(row, 31);
                            indic_f = wh.GetCellContent(row, 32);
                            if (indic_d.Length > 0 || indic_f.Length > 0)
                            {
                                a.Text.UpdateForLanguage("de", indic_d);
                                a.Text.UpdateForLanguage("fr", indic_f);

                            }
                        }


                        indic_d = wh.GetCellContent(row, 34);
                        indic_f = wh.GetCellContent(row, 35);
                        if (indic_d.Length > 0 || indic_f.Length > 0)
                        {
                            Annotation a = new Annotation("GROUP");
                            a.Text.UpdateForLanguage("de", indic_d);
                            a.Text.UpdateForLanguage("fr", indic_f);
                            c.AnnotationList.Add(a);
                        }

                        // EU or CH
                        vague = wh.GetCellContent(row, 36);
                        if (vague.Length > 0)
                        {
                            Annotation a = new Annotation("REMTECH");
                            a.Title = vague;
                            c.AnnotationList.Add(a);
                        }

                        // Questions link 18 -22
                        for (int i = 40; i <= 55; ++i)
                        {
                            string quest = wh.GetCellContent(row, i);
                            if (quest.Length > 0)
                            {
                                Annotation a = new Annotation("QUEST_LNK");
                                a.Title = quest;
                                c.AnnotationList.Add(a);
                                string u = "urn:sdmx:org.sdmx.infomodel.metadataset.TargetValues=CH1_SAKE:MS_Q_MAIN[2012.0].FT_QUEST_VAR.E.2012.";
                                try
                                {
                                  //  string id = string.Format("{0:d5}", int.Parse(quest.Replace(".", string.Empty)));
                                    a.Url = new Uri(u + quest);
                                }
                                catch { }
                            }
                        }
                        */
                    }
                }
            }

        }

    }

    public class SakeExcelColumns
    {
        public int Id { get; set; }
        public int NameFr { get; set; }
        public int NameDe { get; set; }
        public int Decimal { get; set; }
        public int MaxLen { get; set; }
        public int Annual { get; set; }

        public SakeExcelColumns(int year)
        {
            switch (year)
            {
                case 2010:
                    {
                        Id = 3;
                        NameDe = 24;
                        NameFr = 25;
                        MaxLen = 29;
                        Decimal = 32;
                        Annual = 49;
                        break;
                    }
                case 2011:
                    {
                        Id = 5;
                        NameDe = 24;
                        NameFr = 25;
                        MaxLen = 34;
                        Decimal = 37;
                        Annual = 50;
                        break;
                    }
                default:
                    {
                        Id = 7;
                        NameDe = 24;
                        NameFr = 25;
                        MaxLen = 39;
                        Decimal = 42;
                        Annual = 51;
                        break;
                    }
            }
        }

    }

}
